﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Data.SqlClient;
using System.Configuration;

namespace EWDT_CDS_Hub.App_Code
{
    public class CarePerson
    {
        public String Care_person_id { get; set; }
        public String Care_person_name { get; set; }
        public String Care_person_email { get; set; }

        public static List<CarePerson> GetCarePerson()
        {
            List<CarePerson> results = new List<CarePerson>();
            SqlConnection conn = new SqlConnection();

            try
            {
                conn.ConnectionString = ConfigurationManager.ConnectionStrings["EWDTDBString"].ConnectionString;
                conn.Open();

                SqlCommand comm = new SqlCommand();
                comm.Connection = conn;
                comm.CommandText = "SELECT * FROM CarePerson";
                SqlDataReader dr = comm.ExecuteReader();

                while (dr.Read())
                {
                    CarePerson c = new CarePerson();
                    c.Care_person_id = (String)dr["care_person_id"];
                    c.Care_person_name = (String)dr["care_person_name"];
                    c.Care_person_email = (String)dr["care_person_email"];

                    results.Add(c);
                }
            }
            catch (SqlException ex)
            {
                throw ex;
            }
            finally
            {
                conn.Close();
            }

            return results;
        }

        public static int InsertCarePerson(CarePerson c)
        {
            int rowsInserted = 0;
            SqlConnection conn = new SqlConnection();

            try
            {
                conn.ConnectionString = ConfigurationManager.ConnectionStrings["EWDTDBString"].ConnectionString;
                conn.Open();

                SqlCommand comm = new SqlCommand();
                comm.Connection = conn;
                comm.CommandText = "INSERT INTO CarePerson (care_person_id, care_person_name, care_person_email) VALUES (@cpid, @name, @email)";
                comm.Parameters.AddWithValue("@cpid", c.Care_person_id);
                comm.Parameters.AddWithValue("@name", c.Care_person_name);
                comm.Parameters.AddWithValue("@email", c.Care_person_email);
                rowsInserted = comm.ExecuteNonQuery();

            }
            catch (SqlException ex)
            {
                throw ex;
            }
            finally
            {
                conn.Close();
            }

            return rowsInserted;
        }

        public static int UpdateCarePerson(CarePerson c)
        {
            int rowsUpdated = 0;
            SqlConnection conn = new SqlConnection();

            try
            {
                conn.ConnectionString = ConfigurationManager.ConnectionStrings["EWDTDBString"].ConnectionString;
                conn.Open();
                SqlCommand comm = new SqlCommand();
                comm.Connection = conn;
                comm.CommandText = "UPDATE CarePerson SET care_person_name = @name, care_person_email = @email WHERE care_person_id = @id";
                comm.Parameters.AddWithValue("@id", c.Care_person_id);
                comm.Parameters.AddWithValue("@name", c.Care_person_name);
                comm.Parameters.AddWithValue("@email", c.Care_person_email);
                rowsUpdated = comm.ExecuteNonQuery();
            }
            catch (SqlException ex)
            {
                throw ex;
            }
            finally
            {
                conn.Close();
            }
            return rowsUpdated;
        }

        public static CarePerson RetrieveCarePerson(String id)
        {
            CarePerson c = null;
            SqlConnection conn = new SqlConnection();

            try
            {
                conn.ConnectionString = ConfigurationManager.ConnectionStrings["EWDTDBString"].ConnectionString;
                conn.Open();
                SqlCommand comm = new SqlCommand();
                comm.Connection = conn;
                comm.CommandText = "SELECT * FROM CarePerson WHERE care_person_id = @id";
                comm.Parameters.AddWithValue("@id", id);
                SqlDataReader dr = comm.ExecuteReader();

                while (dr.Read())
                {
                    c = new CarePerson();
                    c.Care_person_id = (String)dr["care_person_id"];
                    c.Care_person_name = (String)dr["care_person_name"];
                    c.Care_person_email = (String)dr["care_person_email"];
                }

                dr.Close();
            }
            catch (SqlException ex)
            {
                throw ex;
            }
            finally
            {
                conn.Close();
            }
            return c;
        }

        public static int DeleteCarePerson(String id)
        {
            int rowsDeleted = 0;
            SqlConnection conn = new SqlConnection();

            try
            {
                conn.ConnectionString = ConfigurationManager.ConnectionStrings["EWDTDBString"].ConnectionString;
                conn.Open();
                SqlCommand comm = new SqlCommand();
                comm.Connection = conn;
                comm.CommandText = "DELETE FROM CarePerson WHERE care_person_id = @id";
                comm.Parameters.AddWithValue("@id", id);
                rowsDeleted = comm.ExecuteNonQuery();
            }
            catch (SqlException ex)
            {
                throw ex;
            }
            finally
            {
                conn.Close();
            }
            return rowsDeleted;
        }
    }
}